﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;

namespace Microweb.Utils
{

    public class ZZMMImporter : DMBImporter
    {
        public override string FileName
        {
            get
            {
                return "ZZMM";
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public override string Sqlcmd
        {
            get
            {
                return Sqlcmd = "select ZZMM, ZZMMMC from {0}.dbf";
            }
            set
            {
                throw new NotImplementedException();
            }
        }
    }

    public class DQImporter:DMBImporter
    {
        public override string FileName
        {
            get
            {
                return "DQ";
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public override string Sqlcmd
        {
            get
            {
                return Sqlcmd = "select DQDM, DQMC from {0}.dbf";
            }
            set
            {
                throw new NotImplementedException();
            }
        }
    }

    public abstract class DMBImporter
    {
        private string _rootFolder;
        private IDictionary<string, string> _whiteTableLists;
        public abstract string Sqlcmd { get; set; }
        public abstract string FileName { get; set; }

        /// <summary>
        /// Initializes a new instance of the DMBImporter class.
        /// </summary>
        /// <param name="whiteTableLists"></param>
        public DMBImporter(IDictionary<string, string> whiteTableLists, string rootFolder)
        {
            _whiteTableLists = whiteTableLists;
            _rootFolder = rootFolder;
        }

        public void PreImport(string provinceFolder)
        {
            //强制使用8.3文件格式            
            foreach (var dmb in _whiteTableLists)
            {
                //不需要处理的文件名
                _whiteTableLists[dmb.Key] = dmb.Key;

                //需要处理的文件名
                if (dmb.Key.Length > 8)
                {
                    string dmbFile = Path.Combine(provinceFolder, dmb.Key, ".dbf");
                    string shortPath = EightDotThreeFormat.ToShortPath(dmbFile);
                    string newFilename = Path.GetFileNameWithoutExtension(shortPath);
                    File.Copy(dmbFile, newFilename);
                    _whiteTableLists[dmb.Key] = newFilename;
                }

            }
        }

        public void PostImport(string provinceFolder)
        {
            //清除复制的文件，即key和value不等的文件
            foreach (var dmb in _whiteTableLists)
            {
                if (dmb.Key.Length != dmb.Value.Length)
                {
                    File.Delete(Path.Combine(provinceFolder, dmb.Value, ".dbf"));
                }
            }

        }


        public void Import(string provinceFolder)
        {
            OleDbConnection oConn = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=dBase III", provinceFolder));

            foreach (var dmb in _whiteTableLists)
            {
                Sqlcmd = string.Format(Sqlcmd, dmb.Value);
                OleDbCommand command = new OleDbCommand(Sqlcmd, oConn);

                //open the connection and read in all the airport data from .dbf file into a datatable
                oConn.Open();
                DataTable dt = new DataTable();
                dt.Load(command.ExecuteReader());
                oConn.Close();  //close connection to the .dbf file

                //create a reader for the datatable

                DataTableReader reader = dt.CreateDataReader();

                SqlConnection sqlcn = new SqlConnection("Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;");

                sqlcn.Open();   ///this is my connection to the sql server

                SqlBulkCopy sqlcpy = new SqlBulkCopy(sqlcn);
                sqlcpy.DestinationTableName = dmb.Key;  //copy the datatable to the sql table
                sqlcpy.WriteToServer(dt);


                sqlcn.Close();

                reader.Close();
            }
        }

        public void Import()
        {            
            foreach (var provinceFolder in Directory.GetDirectories(_rootFolder))
            {
                PreImport(provinceFolder);
                Import(provinceFolder);
                PostImport(provinceFolder);
            }
        }
    }

    public class ZPImporter
    {
        public void Import()
        {
            string rootFolder = @"D:\jianghaibin\Documents\Visual Studio 2010\Projects\Microweb.Utils\root";

            // set temporary variable for database connection
            System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();

            // assign database connection string
            _SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

            // Connect to database
            try
            {
                _SqlConnection.Open();
            }
            catch (Exception _Exception)
            {
                // Error occurred while trying to connect to database
                Console.WriteLine(_Exception.Message);
            }

            // Check for valid open database connection before query database
            if (_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
            {
                int _RecCount = 0;

                // load image from file
                foreach (var image in Directory.GetFiles(rootFolder))
                {
                    string filename = Path.GetFileNameWithoutExtension(image);

                    System.Drawing.Image _Image = System.Drawing.Image.FromFile(image);

                    // add new record to database table and get the autoincrement product id
                    _RecCount = InsertUpdateImage(
                        // Pass open database connection to function
                         ref _SqlConnection,
                        // Pass SQL statement to insert new record
                        "INSERT INTO sampletable (ID, image) VALUES (@Id, @image)",
                        // pass image
                        _Image,
                        filename,
                        // image field name
                        "image",
                        // image format
                        System.Drawing.Imaging.ImageFormat.Jpeg);

                    if (_RecCount > 0)
                    {
                        // Record successfully insert into database
                        Console.WriteLine("Image Record added to database table");
                    }
                    else
                    {
                        // Record failed to insert into database
                        Console.WriteLine("Failed to add new record to database table");
                    }

                }
                              
                // close database connection
                _SqlConnection.Close();
            }
        }

        public int InsertUpdateImage(ref System.Data.SqlClient.SqlConnection _SqlConnection, string _SQL, Image _Image, string filename, string _ImageFieldName, ImageFormat _ImageFormat)
        {
            int _SqlRetVal = 0;

            try
            {
                // lets add this record to database
                System.Data.SqlClient.SqlCommand _SqlCommand = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

                // Convert image to memory stream
                System.IO.MemoryStream _MemoryStream = new System.IO.MemoryStream();
                _Image.Save(_MemoryStream, _ImageFormat);

                // Add image as SQL parameter
                System.Data.SqlClient.SqlParameter _SqlParameter
                    = new System.Data.SqlClient.SqlParameter("@" + _ImageFieldName, SqlDbType.Image);

                _SqlParameter.Value = _MemoryStream.ToArray();
                _SqlCommand.Parameters.Add(_SqlParameter);

                _SqlCommand.Parameters["@ID"].Value = filename;

                // Executes a Transact-SQL statement against the connection 
                // and returns the number of rows affected.
                _SqlRetVal = _SqlCommand.ExecuteNonQuery();

                // Dispose command
                _SqlCommand.Dispose();
                _SqlCommand = null;
            }
            catch (Exception _Exception)
            {
                // Error occurred while trying to execute reader
                // send error message to console (change below line to customize error handling)
                Console.WriteLine(_Exception.Message);

                return 0;
            }

            return _SqlRetVal;
        }
    }
}
